﻿using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using UCNKompetenceBors.Model;

namespace UCNKompetenceBors.Database
{
    public class dbLogin
    {
        /// <summary>
        /// Gets a user, based on the userId parameter.
        /// </summary>
        /// <param name="userId">Specifies the ID of the user to be retrieved.</param>
        /// <returns>Returns a LoginUser object.</returns>
        public static LoginUser GetUserById(Guid userId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select us.UserName, mem.Email, mem.CreateDate, prof.fName, prof.lName, prof.phone, prof.bio " +
                                   "from aspnet_Users us, aspnet_Membership mem, UserDetails prof " +
                                   "where us.UserId = @GUID AND mem.UserId = @GUID AND prof.UserID = @GUID";
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@GUID", userId);

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    LoginUser user = null;

                    while (dr.Read())
                    {
                        user = new LoginUser(userId, dr["UserName"].ToString());

                        user.CreatedDate = Convert.ToDateTime(dr["CreateDate"]);
                        user.FirstName = dr["fName"].ToString();
                        user.LastName = dr["lName"].ToString();

                        user.Email = dr["Email"].ToString();

                        if (dr["phone"] != null)
                            user.Phone = dr["phone"].ToString();

                        if (dr["bio"] != null)
                            user.Biography = dr["bio"].ToString();
                    }

                    return user;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets a list of all users.
        /// </summary>
        /// <returns>Returns a list of LoginUser objects.</returns>
        public static List<LoginUser> GetAllUsers()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    List<LoginUser> users = new List<LoginUser>();

                    string query = "select us.UserId, us.UserName, mem.CreateDate, mem.Email, prof.fName, prof.lName, prof.phone, prof.bio " +
                                   "from aspnet_Users us, aspnet_Membership mem, UserDetails prof " +
                                   "where mem.UserId = us.UserId " +
                                   "and prof.UserID = mem.UserId " +
                                   "order by us.UserName asc";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        LoginUser user = new LoginUser(Guid.Parse(dr["UserId"].ToString()), dr["UserName"].ToString());

                        user.CreatedDate = Convert.ToDateTime(dr["CreateDate"]);
                        user.Email = dr["Email"].ToString();
                        user.FirstName = dr["fName"].ToString();
                        user.LastName = dr["lName"].ToString();
                        
                        if (dr["phone"] != null)
                            user.Phone = dr["phone"].ToString();

                        if (dr["bio"] != null)
                            user.Biography = dr["bio"].ToString();

                        users.Add(user);                        
                    }

                    con.Close();
                    con.Dispose();

                    return users;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes a user based on the userId and userName parameters.
        /// </summary>
        /// <param name="userId">Specifies the ID of the user to be deleted.</param>
        /// <param name="userName">Specifies the user name of the user to be deleted.</param>
        public static void DeleteUser(Guid userId, string userName)
        {
            try
            {
                //Deletes any Password Reset Requests as well as any Profile data for the user
                //This is done to avoid issues with Foreign Keys on deleting the user
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "delete from ResetPasswordRequests " +
                                   "where UserID = @userID " +
                                   "delete from UserDetails " +
                                   "where UserID = @userID";

                    SqlCommand cmd = new SqlCommand();

                    cmd.CommandText = query;
                    cmd.Connection = con;
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@userID", userId);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }

                //Deletes the user
                Membership.DeleteUser(userName);
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Adds profile information to an already existing user, based on the input parameters.
        /// </summary>
        /// <param name="userName">Specifies the user name to be used.</param>
        /// <param name="firstName">Specifies the first name to be used.</param>
        /// <param name="lastName">Specifies the last name to be used.</param>
        /// <param name="phone">Specifies the phone to be used.</param>
        /// <param name="bio">Specifies the biography to be used.</param>
        public static void AddProfileInformation(string userName, string firstName, string lastName, string phone, string bio)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "insert into UserDetails (UserID, fName, lName, phone, bio, isMarkedForDeletion) values (@userId, @fName, @lName, @phone, @bio, 0)";
                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    MembershipUser mu = Membership.GetUser(userName);
                    
                    cmd.Parameters.AddWithValue("@userId", (Guid)mu.ProviderUserKey);

                    cmd.Parameters.AddWithValue("@fName", firstName);

                    cmd.Parameters.AddWithValue("@lName", lastName);

                    cmd.Parameters.AddWithValue("@phone", phone);

                    cmd.Parameters.AddWithValue("@bio", bio);

                    con.Open();

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Edits a user, based on the input parameters.
        /// </summary>
        /// <param name="firstName">Specifies the first name to be used.</param>
        /// <param name="lastName">Specifies the last name to be used.</param>
        /// <param name="phone">Specifies the phone to be used.</param>
        /// <param name="bio">Specifies the biography to be used.</param>
        /// <param name="userId">Specifies the ID of the user to be edited</param>
        public static void EditUser(string firstName, string lastName, string phone, string bio, Guid userId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update UserDetails set fName = @fName, lName = @lName, phone = @phone, bio = @bio where UserID = @userId";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@fName", firstName);

                    cmd.Parameters.AddWithValue("@lName", lastName);

                    cmd.Parameters.AddWithValue("@phone", phone);

                    cmd.Parameters.AddWithValue("@bio", bio);

                    cmd.Parameters.AddWithValue("@userId", userId);

                    con.Open();

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Marks users for deletion. 
        /// </summary>
        public static void MarkUsersForDeletion()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update UserDetails " +
                                   "set isMarkedForDeletion = 1 ";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();
                    con.Dispose();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Starts the reset users process, based on the input parameters.
        /// </summary>
        /// <param name="startDate">Specifies the start date to be used.</param>
        /// <param name="endDate">Specifies the end date to be used.</param>
        public static void StartResetUsers(DateTime startDate, DateTime endDate)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update ResetUsers " +
                                   "set resetUsersLastStartDate = @resetUsersStartDate, resetUsersEndDate = @resetUsersEndDate, resetUsersIsStarted = 1 " +
                                   "where id = 1";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@resetUsersStartDate", startDate);

                    cmd.Parameters.AddWithValue("@resetUsersEndDate", endDate);

                    con.Open();

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Gets the startDate, endDate and status of the reset users process.
        /// </summary>
        /// <returns>Returns a ResetUsers object.</returns>
        public static ResetUsers GetResetUsers()
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {
                    string query = "select resetUsersLastStartDate, resetUsersEndDate, resetUsersIsStarted, resetUsersNewsTitle, resetUsersNewsText, resetUsersNewsLocalizedTitle, resetUsersNewsLocalizedText " +
                                   "from ResetUsers " +
                                   "where id = 1";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    con.Open();

                    SqlDataReader dr = cmd.ExecuteReader();

                    ResetUsers resetUsers = null;

                    while (dr.Read())
                    {
                        resetUsers = new ResetUsers();

                        if (!Convert.IsDBNull(dr["resetUsersLastStartDate"]))
                            resetUsers.StartDate = Convert.ToDateTime(dr["resetUsersLastStartDate"]);

                        if (!Convert.IsDBNull(dr["resetUsersEndDate"]))
                            resetUsers.EndDate = Convert.ToDateTime(dr["resetUsersEndDate"]);

                        if (!Convert.IsDBNull(dr["resetUsersIsStarted"]))
                            resetUsers.ResetUsersIsStarted = Convert.ToBoolean(dr["resetUsersIsStarted"]);

                        resetUsers.NewsTitle = dr["resetUsersNewsTitle"].ToString();
                        resetUsers.NewsText = dr["resetUsersNewsText"].ToString();

                        resetUsers.NewsLocalizedTitle = dr["resetUsersNewsLocalizedTitle"].ToString();
                        resetUsers.NewsLocalizedText = dr["resetUsersNewsLocalizedText"].ToString();
                    }

                    return resetUsers;
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Deletes all users marked for deletion.
        /// </summary>
        public static void DeleteUsersMarkedForDeletion()
        {
            string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connString))
            {
                //Sets the SqlCommand to the Stored Procedure used to get the user name
                SqlCommand cmd = new SqlCommand("sprocDeleteUsersMarkedForDeletion", con);
                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();
                cmd.ExecuteNonQuery();

                con.Close();
                con.Dispose();
            }
        }

        /// <summary>
        /// Removes the mark for deletion on a user, based on the userId parameter.
        /// </summary>
        /// <param name="userId">Specifies the ID of the user to be unmarked.</param>
        public static void UnmarkUserForDeletion(Guid userId)
        {
            try
            {
                string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;

                using (SqlConnection con = new SqlConnection(connString))
                {

                    string query = "update UserDetails " +
                                   "set isMarkedForDeletion = 0 " +
                                   "where UserID = @userId";

                    SqlCommand cmd = new SqlCommand(query, con);
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.AddWithValue("@userId", userId);

                    con.Open();

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException e)
            {
                //insert logging here
                throw;
            }
            catch (Exception e)
            {
                //insert logging here
                throw;
            }
        }

        /// <summary>
        /// Creates an SQL job to delete users marked for deletion.
        /// </summary>
        /// <param name="startDate">Specifies the starting date of the SQL job.</param>
        //public static void CreateDeletionJob(DateTime startDate)
        //{
        //    try
        //    {
        //        Server server = new Server();

        //        Operator op = new Operator(server.JobServer, "UserPurge_Operator");

        //        op.Create();

        //        //skriv en e-mail adresse til den admin der skal have fejlbeskeder / succesbeskeder
        //        //opret funktionalitet der sender mail til ovenstående adresse ved fejl / succes

        //        Job job = new Job(server.JobServer, "UserPurge_Job");

        //        job.Create();

        //        JobStep jobStep = new JobStep(job, "UserPurge_Job_Step");

        //        jobStep.Command = "sprocDeleteUsersMarkedForDeletion";
        //        jobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
        //        jobStep.OnFailAction = StepCompletionAction.QuitWithFailure;

        //        jobStep.Create();

        //        JobSchedule jobSchedule = new JobSchedule(job, "UserPurge_Job_Schedule");

        //        jobSchedule.FrequencyTypes = FrequencyTypes.OneTime;

        //        TimeSpan startHour = new TimeSpan(11, 0, 0);
        //        jobSchedule.ActiveStartTimeOfDay = startHour;

        //        TimeSpan endHour = new TimeSpan(12, 0, 0);
        //        jobSchedule.ActiveEndTimeOfDay = endHour;

        //        jobSchedule.FrequencyInterval = 1;
        //        jobSchedule.ActiveStartDate = startDate;

        //        jobSchedule.Create();
        //    }
        //    catch (SqlException e)
        //    {
        //        //insert logging here
        //        throw;
        //    }
        //    catch (Exception e)
        //    {
        //        //insert logging here
        //        throw;
        //    }
        //}

        /// <summary>
        /// Creates a password request, based on the userName parameter.
        /// </summary>
        /// <param name="userName">Specifies the user name of the user, the password request is created for.</param>
        /// <returns>Returns a PasswordRequest object.</returns>
        public static PasswordRequest RequestPasswordRecovery(string userName)
        {
            string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connString))
            {
                PasswordRequest passRec = new PasswordRequest();

                //Sets the SqlCommand to the Stored Procedure used to check for the user
                SqlCommand cmd = new SqlCommand("sprocResetPassword", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramUserName = new SqlParameter("@userName", userName);
                cmd.Parameters.Add(paramUserName);

                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    //ReturnCode is 1 if the user was found and 0 if not - this translates into 1 = true 0 = false
                    if (Convert.ToBoolean(dr["ReturnCode"]))
                    {
                        passRec.UniqueId = Guid.Parse(dr["UniqueId"].ToString());
                        passRec.Email = dr["Email"].ToString();
                        passRec.UserExists = Convert.ToBoolean(dr["ReturnCode"]);
                    }
                    else
                    {
                        passRec.UserExists = Convert.ToBoolean(dr["ReturnCode"]);
                    }
                }

                con.Close();
                con.Dispose();

                return passRec;
            }
        }

        /// <summary>
        /// Deletes a password recovery request, based on the guid parameter.
        /// </summary>
        /// <param name="guid">Specifies the guid of the request to be deleted.</param>
        public static void DeletePasswordRecoveryRequest(string guid)
        {
            string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connString))
            {
                //Sets the SqlCommand to the Stored Procedure used to get the user name
                SqlCommand cmd = new SqlCommand("sprocDeletePasswordResetRequest", con);
                cmd.CommandType = CommandType.StoredProcedure;
                int rowsAffected;

                SqlParameter paramGUID = new SqlParameter("@GUID", guid);
                cmd.Parameters.Add(paramGUID);

                con.Open();
                rowsAffected = cmd.ExecuteNonQuery();

                con.Close();
                con.Dispose();
            }
        }

        /// <summary>
        /// Gets a user name, based on the guid parameter.
        /// </summary>
        /// <param name="guid">Specifies the guid to be used.</param>
        /// <returns>Returns a string containig the resulting user name</returns>
        public static string GetUserNameByGUID(string guid)
        {
            string result = String.Empty;

            string connString = ConfigurationManager.ConnectionStrings["ConnStringDB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connString))
            {
                //Sets the SqlCommand to the Stored Procedure used to get the user name
                SqlCommand cmd = new SqlCommand("sprocGetUserNameFromGUID", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter paramGUID = new SqlParameter("@GUID", guid);
                cmd.Parameters.Add(paramGUID);

                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    result = dr["UserName"].ToString();
                }

                con.Close();
                con.Dispose();
            }

            return result;
        }

        /// <summary>
        /// Checks whether or not a password reset link is valid, based on the guid parameter.
        /// </summary>
        /// <param name="guid">Specifies the guid of the password reset link to be used.</param>
        /// <returns>Returns a boolean specifying wheter or not the password reset link is valid.</returns>
        public static bool PasswordResetLinkIsValid(string guid)
        {
            List<SqlParameter> paramList = new List<SqlParameter>()
            {
                new SqlParameter()
                {
                    ParameterName = "@GUID",
                    Value = guid
                }
            };

            return dbGeneral.ExecuteStoredProc("sprocPasswordResetLinkValid", paramList);
        }
    }
}